﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DefaulArchitecture.Domain;
using System.Data.SqlClient;
using System.Configuration;
using DefaulArchitecture.Exception;
using System.Data.Common;

namespace DefaulArchitecture.MySQLDAL
{
    public abstract class BaseDAL<T> : IDAL<T>, IDALTransaction where T : IDomain
    {
        #region Attributes

        private string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
        private DbConnection _connection;
        private DbTransaction _transaction;
        private bool isUsingTransaction;

        private DbProviderFactory provider = null;

        #endregion

        #region Constructors

        /// <summary>
        /// Starts the SqlConnection.
        /// The default connection string will be gotten from configuration manager in the key "ConnStr".
        /// </summary>
        public BaseDAL()
        {
            this.connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
            provider = DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings["ConnStr"].ProviderName);
            _connection = new SqlConnection(this.connStr);

            //_connection = provider.CreateConnection();
            //_connection.ConnectionString = this.connStr;
            //DbParameter p = provider.CreateParameter();
            //p.ParameterName = "@nome";
            //p.Value = 123;

            //Dictionary<string, object> parametros = new Dictionary<string, object>();
            //parametros.Add("nome", "asdadasds");

            isUsingTransaction = false;
            this.Lazy = true;
        }

        /// <summary>
        /// Starts the SqlConnection.
        /// </summary>
        /// <param name="connStr">Connection string used to connect in database</param>
        public BaseDAL(string connStr)
        {
            this.connStr = connStr;
            _connection = new DbConnection(this.connStr);
            isUsingTransaction = false;
        }

        #endregion

        #region Properties

        public string ConnectionString
        {
            get { return this.connStr; }
        }

        public bool Lazy
        {
            get;
            set;
        }

        #endregion

        #region Abstract Methods

        /// <summary>
        /// Inserts a new IModel object in the database.
        /// </summary>
        /// <param name="model">IModel object that will be inserted into database</param>
        /// <returns>The id of the new record</returns>
        public abstract int Insert(T model);

        /// <summary>
        /// Updates an IModel object value in the database.
        /// </summary>
        /// <param name="model">IModel object that will be used to update a record</param>
        /// <returns>If the update operation was successful</returns>
        public abstract bool Update(T model);

        /// <summary>
        /// Removes a register in the database.
        /// </summary>
        /// <param name="id">Id of the record</param>
        /// <returns>If the record was removed</returns>
        public abstract bool Delete(int id);

        /// <summary>
        /// Retrieves all data from a table.
        /// </summary>
        /// <returns>A list with all IModel objects filled with the retrieved data</returns>
        public abstract List<T> SelectAll();

        /// <summary>
        /// Retrieves a register from a table
        /// </summary>
        /// <param name="id">Id of the record</param>
        /// <returns>An IModel object filled with the retrieved data</returns>
        public abstract T SelectById(int id);

        /// <summary>
        /// Build and IModel object through the retrieved data.
        /// </summary>
        /// <param name="reader">Retrieved SqlDataReader</param>
        /// <returns>n IModel object filled with the retrieved data</returns>
        protected abstract T BuildObject(DbDataReader reader);

        #endregion

        #region Public Methods

        /// <summary>
        /// Sets the SqlTransaction object
        /// </summary>
        /// <param name="transaction">The transaction that will be set in this instance</param>
        public void BeginTransaction(DbTransaction transaction)
        {
            this._transaction = transaction;
            this.isUsingTransaction = true;
        }

        /// <summary>
        /// Unset the SqlTransaction object
        /// </summary>
        public void EndTransaction()
        {
            this.isUsingTransaction = false;
            this._transaction = null;
        }

        #endregion

        #region Protected Methods

        /// <summary>
        /// Calls the ExecuteNonQuery command.
        /// </summary>
        /// <param name="cmdText">The SQL command text</param>
        /// <param name="parameters">The parameters of the command</param>
        /// <param name="isProcedure">Sets if the command will be a procedure or not. Default: true</param>
        /// <returns></returns>
        protected int ExecuteNonQuery(string cmdText, List<DbParameter> parameters, bool isProcedure = true)
        {
            try
            {
                DbCommand cmd = default(DbCommand);
                int ret = default(int);

                if (!this.isUsingTransaction)
                {
                    this.OpenConnection();
                    cmd = new DbCommand(cmdText, this._connection);
                }
                else
                {
                    cmd = new DbCommand(cmdText, this._transaction.Connection);
                    cmd.Transaction = this._transaction;
                }

                if (isProcedure)
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                this.SetParametersInCommand(parameters, cmd);
                ret = cmd.ExecuteNonQuery();

                return ret;
            }
            catch (System.Exception e)
            {
                throw new DALException(e.Message, "ExecuteNonQuery");
            }
            finally
            {
                if (!this.isUsingTransaction)
                    this.CloseConnection();
            }
        }

        /// <summary>
        /// Calls the ExecuteScalar command.
        /// </summary>
        /// <param name="cmdText">The SQL command text</param>
        /// <param name="parameters">The parameters of the command</param>
        /// <param name="isProcedure">Sets if the command will be a procedure or not. Default: true</param>
        /// <returns></returns>
        protected object ExecuteScalar(string cmdText, List<DbParameter> parameters, bool isProcedure = true)
        {
            try
            {
                DbCommand cmd = default(DbCommand);
                object ret = default(object);

                if (!this.isUsingTransaction)
                {
                    this.OpenConnection();
                    cmd = new DbCommand(cmdText, this._connection);
                }
                else
                {
                    cmd = new DbCommand(cmdText, this._transaction.Connection);
                    cmd.Transaction = this._transaction;
                }

                if (isProcedure)
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                this.SetParametersInCommand(parameters, cmd);
                ret = cmd.ExecuteScalar();

                return ret;
            }
            catch (System.Exception e)
            {
                throw new DALException(e.Message, "ExecuteScalar");
            }
            finally
            {
                if (!this.isUsingTransaction)
                    this.CloseConnection();
            }
        }

        /// <summary>
        /// Calls the ExecuteReader command and fills a list with the retrieved data.
        /// </summary>
        /// <param name="cmdText">The SQL command text</param>
        /// <param name="parameters">The parameters of the command</param>
        /// <param name="isProcedure">Sets if the command will be a procedure or not. Default: true</param>
        /// <returns>A list filled with all retrieved data.</returns>
        protected List<T> ExecuteSelectMany(string cmdText, List<DbParameter> parameters, bool isProcedure = true)
        {
            DbDataReader reader = null;
            try
            {
                List<T> list = new List<T>();
                DbCommand cmd = new DbCommand(cmdText, this._connection);

                if (!this.isUsingTransaction)
                {
                    this.OpenConnection();
                    cmd = new DbCommand(cmdText, this._connection);
                }
                else
                {
                    cmd = new DbCommand(cmdText, this._transaction.Connection);
                    cmd.Transaction = this._transaction;
                }

                if (isProcedure)
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                this.SetParametersInCommand(parameters, cmd);
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        list.Add(this.BuildObject(reader));
                    }
                }

                return list;
            }
            catch (System.Exception e)
            {
                throw new DALException(e.Message, "ExecuteSelectMany");
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close(); reader.Dispose();
                }
                if (!this.isUsingTransaction)
                    this.CloseConnection();
            }
        }

        /// <summary>
        /// Calls the ExecuteReader command to select the first retrieved data.
        /// </summary>
        /// <param name="cmdText">The SQL command text</param>
        /// <param name="parameters">The parameters of the command</param>
        /// <param name="isProcedure">Sets if the command will be a procedure or not. Default: true</param>
        /// <returns>An object filled with the retrieved data.</returns>
        protected T ExecuteSelectFirst(string cmdText, List<DbParameter> parameters, bool isProcedure = true)
        {
            DbDataReader reader = null;
            try
            {
                T model = default(T);
                DbCommand cmd = new DbCommand(cmdText, this._connection);


                if (!this.isUsingTransaction)
                {
                    this.OpenConnection();
                    cmd = new DbCommand(cmdText, this._connection);
                }
                else
                {
                    cmd = new DbCommand(cmdText, this._transaction.Connection);
                    cmd.Transaction = this._transaction;
                }

                if (isProcedure)
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                this.SetParametersInCommand(parameters, cmd);
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    if (reader.Read())
                    {
                        model = this.BuildObject(reader);
                    }
                }

                return model;
            }
            catch (System.Exception e)
            {
                throw new DALException(e.Message, "ExecuteSelectFirst");
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close(); reader.Dispose();
                }
                if (!this.isUsingTransaction)
                    this.CloseConnection();
            }
        }

        /// <summary>
        /// Verify if a column exists in SqlDataReader.
        /// </summary>
        /// <param name="reader">Reader you want to verify</param>
        /// <param name="columnName">Name of column you want to check if exists</param>
        /// <returns></returns>
        protected bool ColumnExists(DbDataReader reader, string columnName)
        {
            reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= '" + columnName + "'";
            return (reader.GetSchemaTable().DefaultView.Count > 0);
        }

        #endregion

        #region Private Methods

        /// <summary>
        /// Sets paramenters in a command
        /// </summary>
        /// <param name="parameters">Parameters that will be set in the command</param>
        /// <param name="command">Command that will receive the parameters</param>
        private void SetParametersInCommand(List<DbParameter> parameters, DbCommand command)
        {
            if (parameters != null && parameters.Count > 0)
            {
                foreach (var param in parameters)
                {
                    command.Parameters.Add(param);
                }
            }
        }

        /// <summary>
        /// Opens the connection.
        /// </summary>
        private void OpenConnection()
        {
            if (_connection.State != System.Data.ConnectionState.Open)
                _connection.Open();
        }

        /// <summary>
        /// Closes the connection.
        /// </summary>
        private void CloseConnection()
        {
            if (_connection.State != System.Data.ConnectionState.Closed)
                _connection.Close();
        }

        #endregion

        public virtual bool Exists(T obj)
        {
            return false;
        }
    }
}
